-- 게시글 테이블
drop table safari_buyboard;
create table safari_buyboard(
buy_no number primary key,
buy_title varchar2(70) not null,
buy_goods varchar2(60) not null,
buy_tradeArea varchar2(50) not null,
buy_content varchar2(3000) not null,
buy_hits number default 0 not null,
buy_time_posted date not null,
id varchar2(20) not null,
buyboard_level number default 0 not null,
CONSTRAINT buyboard_fk_id foreign key(id) REFERENCES safari_member(id)
);
alter table safari_buyboard RENAME COLUMN buycomment_ref TO buyboard_ref;
alter table safari_buyboard RENAME COLUMN buycomment_level TO buyboard_level;
alter table safari_buyboard add(buycomment_level number default 0 not null);
alter table safari_buyboard drop column buyboard_ref;
-- buyboard_ref 에 buy_no 값 넣기
update safari_buyboard set buyboard_ref=buy_no;

select * from safari_buyboard;

--게시물 쓰기
insert into safari_buyboard(buy_no, buy_title, buy_goods, buy_tradeArea, buy_content, buy_hits, buy_time_posted, id)
	values(1,'삽니다~','핸드폰','용인','갤럭시 S3 중고 사요~',1,sysdate,'audrlek');
insert into safari_buyboard(buy_no, buy_title, buy_goods, buy_tradeArea, buy_content, buy_hits, buy_time_posted, id)
	values(2,'중고 노트북 삽니다.','HP650','종로','HP650 시리즈 노트북 삽니다.',1,sysdate,'duwjddlek');

--게시글 보여주기
 select s.buy_no, s.buy_title, m.name, m.id, m.email, s.buy_goods, s.buy_tradeArea, s.buy_content, s.buy_hits, s.buy_time_posted 
	    from safari_buyboard s, safari_member m
	    where s.buy_no='55' and m.id=s.id

--최신글 보여주기(현재 안씀임)
select max(buy_no) from safari_buyboard order by buy_no desc

--게시글 목록 보여주기
select s.buy_no, s.buy_title, m.name, m.id, s.buy_hits, s.buy_time_posted from
	    (select buy_no,id,buy_title,buy_hits,buy_time_posted,ceil(rownum/5) as page from
	    (select buy_no,id,buy_title,to_char(buy_time_posted,'YYYY.MM.DD') 
	    as buy_time_posted, buy_hits from safari_buyboard order by buy_no desc
	    ))s, safari_member m
	    where page=1 and m.id=s.id
--댓글 테이블
drop table safari_buycomment;
create table safari_buycomment(
buycomment_no number primary key,
buycomment varchar2(1000) not null,
buycomment_time_posted date not null,
buycomment_ref number not null,
buycomment_level number not null,
CONSTRAINT buyboard_fk_no foreign key(buycomment_ref) REFERENCES safari_buyboard(buy_no)
);
alter table safari_buycomment add(id varchar2(20) default'' not null);
alter table safari_buycomment drop column id;
select * from SAFARI_BUYCOMMENT;
--댓글 리스트(목록) 보여주기
select c.buycomment_no, c.buycomment, c.buycomment_time_posted, c.buycomment_ref, c.buycomment_level, m.id from
	(select buycomment_no, id, buycomment, buycomment_ref, buycomment_level, buycomment_time_posted, ceil(rownum/5) as page from
	(select buycomment_no, id, buycomment, buycomment_ref, buycomment_level, to_char(buycomment_time_posted,'YYYY.MM.DD')
	as buycomment_time_posted from safari_buycomment
	))c, safari_member m, safari_buyboard b
	where page=1 and b.buy_no=1 and c.buycomment_ref=b.buy_no and m.id=c.id

--댓글 보여주기(페이징빈 적용X)
select c.buycomment_no, c.buycomment, c.buycomment_time_posted, c.buycomment_ref, c.buycomment_level, m.id
	    from safari_buycomment c, safari_member m, safari_buyboard b
	    where b.buy_no='1' and c.buycomment_ref=b.buy_no and m.id=b.id
	
buy_no number not null,
--제약조건 삭제
ALTER TABLE safari_buycomment drop CONSTRAINT buyboard_fk_no;

--댓글 쓰기		
insert into safari_buycomment(buycomment_no, buycomment, buycomment_time_posted, buycomment_ref, buycomment_level, id)
	values(1, '갤럭시S3 중고로 팝니다~', sysdate, '1', 0+1, 'audrlek');
insert into safari_buycomment(buycomment_no, buycomment, buycomment_time_posted, buycomment_ref, buycomment_level, id)
	values(2, '제 갤럭시S3 중고 팔게요~ 전화번호는 010-0000-0000 입니다ㅋ', sysdate, '1', 0+1, 'duwjddlek');
insert into safari_buycomment(buycomment_no, buycomment, buycomment_time_posted, buycomment_ref, buycomment_level, id)
	values(3, '노트북 팝니다~ 010-0000-0000로 연락주세요~', sysdate, '2', 0+1, 'audrlek');

--댓글 삭제
delete from safari_buycomment;
	    
-- 시퀀스 생성
create sequence buyboard_seq nocache;
create sequence buycomment_seq nocache;
--시퀀스 삭제
drop sequence buyboard_seq;
drop sequence buycomment_seq;
-- 현 시퀀스의 다음 값을 조회 
select sequence buyboard_seq.nextval from dual;
select sequence buycomment_seq.nextval from dual;